package com.xunwulian.base.util;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by Administrator on 2017/4/19.
 */
public class ExcelUtil2 {

    // 日志工具类
    private MyLogUtil myLogUtil = new MyLogUtil(ExcelUtil2.class);

    // bean工具类
    private BeanUtil beanUtil = new BeanUtil();

    // bean工具类3
    private BeanUtil3 beanUtil3 = new BeanUtil3();

    private int showCount = 50000;

    public <T> Workbook parseEntities(Collection<T> entities, Map<String, String> names) {

        HSSFWorkbook workbook = new HSSFWorkbook();

        // 创建一个单元的样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
        cellStyle.setFillForegroundColor(HSSFColor.BLACK.index);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        // 设置字体
        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontName("宋体");
        cellFont.setFontHeightInPoints((short) 14);
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);

        int sheetCount = (entities.size() / showCount) + (entities.size() % showCount == 0 ? 0 : 1);

        List<String> attrNames = new ArrayList<>(names.keySet());

        Iterator<T> iterator = entities.iterator();

        for (int sheetIndex = 0; sheetIndex < sheetCount; ) {

            HSSFSheet sheet = workbook.createSheet("第" + (++sheetIndex) + "页");

            HSSFRow rowTitle = sheet.createRow(0);
            for (int i = 0; i < attrNames.size(); i++) {
                HSSFCell cellTitle = rowTitle.createCell(i);
                cellTitle.setCellStyle(cellStyle);
                cellTitle.setCellValue(names.get(attrNames.get(i)));
            }

            int index = 0;
            while (iterator.hasNext() && index < showCount) {

                T entity = iterator.next();

                HSSFRow row = sheet.createRow(++index);
                Map<String, String> mapAttr = beanUtil.beanToMap(entity);

                for (int i = 0; i < attrNames.size(); i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(mapAttr.get(attrNames.get(i)));
                }
            }
        }

        return workbook;
    }

    public File writeToFile(Workbook workbook, String root, String path) throws IOException {
        return writeToFile(workbook, new File(root, path));
    }

    public File writeToFile(Workbook workbook, String path) throws IOException {
        return writeToFile(workbook, new File(path));
    }

    public File writeToFile(Workbook workbook, File file) throws IOException {
        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        fos.flush();
        fos.close();
        return file;
    }

    /**
     * 通过传入excel文件的完整路径。然后解析excel文件
     *
     * @param path
     * @return
     */
    public Workbook loadExcel(String path) {
        return loadExcel(new File(path));
    }

    public Workbook loadExcel(File u) {

        Workbook workbook = null;

        try {
            // XSSFWorkbook 用来加载后缀名为 .xlsx 文件的
            workbook = new XSSFWorkbook(new FileInputStream(u));
        } catch (Exception ex) {
            try {
                // HSSFWorkbook 用来加载后缀名为 .xls 文件的
                workbook = new HSSFWorkbook(new FileInputStream(u));
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }

            ex.printStackTrace();

        }

        return workbook;
    }

    /**
     * 得到每个cell里面的值
     */
    public String getValue(Cell cell) {

        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

            BigDecimal bd = new BigDecimal(cell.getNumericCellValue());

            return bd.toPlainString();
        } else {

            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
     * workbook 转换为 java对象
     *
     * @param workbook
     * @return
     */
    public <T> List<T> parseExcel(Workbook workbook, Class<T> c, List<ExcelColumnWrapper> ecws) {

        List<T> list = new ArrayList();

        // excel文件里面有很多的sheet, 要遍历每一张sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {

            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }

            // 每张sheet里面有很多的row, 要遍历每行
            for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                HashMap<String, String> map = new HashMap<>();
                // 每行都有很多列, 要遍历每列
                for (ExcelColumnWrapper ecw : ecws) {
                    ecw.cell_value = getValue(row.getCell(ecw.index));
                    ecw.fix();
//                    myLogUtil.log(beanUtil3.beanToJson(ecw));
                    if (StringUtils.isNotBlank(ecw.attr_name)) {
                        map.put(ecw.attr_name, ecw.attr_value);
                    }
                }
//                myLogUtil.log(beanUtil3.beanToJson(map));
                T elem = beanUtil.mapToBean(map, c);
//                myLogUtil.log(beanUtil3.beanToJson(elem));
                list.add(elem);
            }
        }

        return list;
    }

    public static class ExcelColumnWrapper {

        public int index;
        public String cell_name;
        public String cell_value;
        public String attr_name;
        public String attr_value;
        public CellConvert convert;

        public ExcelColumnWrapper(int index, String attr_name) {
            this.index = index;
            this.attr_name = attr_name;
        }

        public ExcelColumnWrapper(int index, String attr_name, CellConvert convert) {
            this(index, attr_name);
            this.convert = convert;
        }

        public void fix() {
            if (convert != null) {
                attr_value = convert.convert(cell_value);
            } else {
                attr_value = cell_value;
            }
        }
    }

    public interface CellConvert {
        String convert(String cell_value);
    }

    public void setShowCount(int showCount) {
        this.showCount = showCount > 65535 ? 65535 : showCount;
        this.showCount = showCount <= 0 ? this.showCount : showCount;
    }

    public static void main(String[] args) {
        String path = "C:\\Users\\yankaili\\IdeaProjects\\xunwulian\\xwl-web\\target\\xwl-web\\upload\\7602c4fe-84fd-41b8-af57-0926e89814fb.xlsx";
        ExcelUtil excelUtil = new ExcelUtil();
        Workbook wb = excelUtil.loadExcel(path);
        Sheet sheet = wb.getSheetAt(0);

        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            BigDecimal bd = new BigDecimal(row.getCell(0).getNumericCellValue());
            System.out.println(row.getCell(0).getCellType() + "," + bd.toPlainString());

        }
    }

}
